
clear all

/// Compute county-level Representation Indices


// Combine lower and upper house Representation Index measures
cd "$OutputPath/"
use county_pop_elec, clear

merge 1:1 year fstate fcounty using county_pop_elec_upper
drop if _m==2

drop if fcounty==. // Utah district 72 was not assigned to any blocks in in census redistricting program. 

tempfile base
save `base', replace

// Load government finance data and merge
use "$OutputPath/GovFin_allincounty", clear

keep fstate fcounty year rev_tot rev_fed rev_state stateigreducation stateigrtaxrelief stateigrhealthhos stateigrhighways stateigrtransitsub stateigrhouscomdev stateigrpublicwelf stateigrsewerage rev_locgov exp_tot exp_edu ngov_munic ngov_special ngov_school state_tot_igr_local state_tot_exp


* collapse NYC counties to harmonize NYC data  -- collapsed to fcounty==61 in 2007 and 2017 but not 2012
gen nyc=(fstate==36 & (fcounty==5 | fcounty==61 | fcounty==81 | fcounty==85 | fcounty==47))

tempfile all
save `all'

keep if nyc==1
replace fcounty=61

collapse (sum) rev_tot rev_fed rev_state stateigreducation stateigrtaxrelief stateigrhealthhos stateigrhighways stateigrtransitsub stateigrhouscomdev stateigrpublicwelf stateigrsewerage rev_locgov exp_tot exp_edu  ngov_munic ngov_special ngov_school (mean) state_tot_exp state_tot_igr_local, by(year fstate fcounty)

append using `all'
drop if nyc==1
drop nyc 

* Shannon County, South Dakota (FIPS 46 113) was renamed Oglala Lakota County (FIPS 46 102) 
replace fcounty=113 if fstate==46 & fcounty==102 

gen exp_notedu=exp_tot-exp_edu
gen stateigrnotedu=rev_state-stateigreducation

foreach i in education notedu healthhos highways transitsub houscomdev publicwelf sewerage taxrelief {
	rename stateigr`i' rev_state`i'
}

merge 1:1 year fstate fcounty  using `base', gen(m_gov)

* drop Alaska census areas that have changing borders
drop if fstate==2 & (fcounty==158 | fcounty==195 | fcounty==198 | fcounty==201 | fcounty==270 | fcounty==275 | fcounty==280)
drop if m_gov==1 
drop m_gov 


tempfile base
save `base', replace

// Merge with county-level population and covariates (ACS 5 year estimates from NHGIS)


save `base', replace

use "$OutputPath/nhgis_county", clear

keep fstate fcounty year male female population whitenh blacknh hispanic unemployed age5_17 age65_up vap bachelors belowpoverty medhhinc 

* collapse NYC counties to harmonize NYC data  -- was already collapsed to fcounty==61 in 2007 and 2017 but not 2012
gen nyc=(fstate==36 & (fcounty==5 | fcounty==61 | fcounty==81 | fcounty==85 | fcounty==47))

tempfile all
save `all'

keep if nyc==1
replace fcounty=61

egen totpop=total(pop)
gen ppop=pop/totpop

replace medhhinc=medhhinc*ppop // use population-weighted average of median household income

collapse (sum) male female population whitenh blacknh hispanic unemployed age5_17 age65_up vap bachelors belowpoverty medhhinc , by(year fstate fcounty)

append using `all'
drop if nyc==1

* Shannon County, South Dakota (FIPS 46 113) was renamed Oglala Lakota County (FIPS 46 102) 
replace fcounty=113 if fstate==46 & fcounty==102 

merge 1:1 year fstate fcounty using `base', gen(m_nhgis)
drop if m_nhgis==1
drop m_nhgis

egen statepop=total(pop), by(year fstate)
replace RRI=RRI/(totaldistricts/statepop)

rename population pop 

gen censusyear=2000 if year<=2012 
replace censusyear=2010 if year==2017

drop _m 

save `base', replace

* Import updated version from All About Redistricting website: 
clear
import excel "$InputPath/StatesAndCyclesData_production-20240301a.xls", firstrow sheet(StatesAndCyclesData_production-) 
keep if (Level== "State Lower" | (State=="NE" & Level=="State Upper"))  & CycleYear<2020


gen drawnby_leg_gov = (Drawnby== "Legislature" | Drawnby=="Governor" | Drawnby=="Political commission")

rename (State CycleYear) (state_abbrev censusyear)

keep state_abbrev drawnby_leg_gov censusyear

duplicates drop censusyear state_abbrev, force

statastates , a(state_abbrev) 
drop state_name state_n _m
rename state_f fstate

merge m:m fstate censusyear using `base', gen(m3)
drop if m3!=3 // DC 
drop m3

// merge with lobbying from Payson (2020) 
cd "$OutputPath/"
merge 1:1 year fstate fcounty using Payson_Lobbying, gen(m_lobby)

drop if m_lobby==2 // NYC boroughs -- added manually below
replace lobby=1 if fstate==36 & fcounty==61 & year<2017
drop m_lobby

replace lobby=0 if lobby==. & year<2017  // indicator is for known lobbying among cities

// merge with number of school districts per county from NCES
cd "$OutputPath/"
merge m:1 fstate fcounty using NCES_nschool_per_county, gen(m_nschool)
drop if m_nschool==2
drop m_nschool


// harmonize some missing vars // rename
replace censusyear=2020 if year==2017
rename (nmember nmember_u) (nreps nreps_u)

// add state abbrev
statastates, n(state)

drop if _m==2 //DC
drop _m state_fips  



